﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace MyVideoTek
{
    public class SqliteData
    {
        #region Attributs
        private string nameBdd = @"Data Source=Data/mvt.db3;"; // Nom de la BDD
        private DataTable resultTable;                      //Table obtenu après requete
        #endregion

        #region Requete Select
        public DataTable Select(string requete)
        {
            this.resultTable = new DataTable();
            //Tente une connection
            try
            {
                DataSet dataSet = new DataSet();
                    SQLiteConnection ObjConnection = new SQLiteConnection(this.nameBdd);
                    SQLiteCommand ObjCommand = new SQLiteCommand(requete, ObjConnection);
                    ObjCommand.CommandType = CommandType.Text;
                    SQLiteDataAdapter ObjDataAdapter = new SQLiteDataAdapter(ObjCommand);

                    ObjDataAdapter.Fill(dataSet);
                    this.resultTable = dataSet.Tables[0];
                    ObjConnection.Dispose();
            }

            //Une erreur s'est produite
            catch (Exception Ex)
            {
                //Affichage d'une eventuel erreur
                MessageBox.Show("Impossible de ce connecter à la base de donnée. Erreur :\n " + Ex + "\n", "Impossible de se connter à la base de donnée.", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return this.resultTable;
        }
        #endregion

        #region Constructeur
        public void Insert(string requete)
        {
            DataSet resultat = new DataSet();
            //Tente une connection
            try
            {
                SQLiteConnection ObjConnection = new SQLiteConnection(this.nameBdd);
                SQLiteCommand ObjCommand = new SQLiteCommand(requete, ObjConnection);
                ObjCommand.CommandType = CommandType.Text;
                SQLiteDataAdapter ObjDataAdapter = new SQLiteDataAdapter(ObjCommand);
                DataSet dataSet = new DataSet();

                //Libère la mémoire
                ObjDataAdapter.Fill(resultat);
                ObjConnection.Dispose();
            }

            //Une erreur s'est produite
            catch (Exception Ex)
            {
                MessageBox.Show(requete + Ex.ToString(), "Impossible de se connter à la base de donnée.", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        #endregion

        #region Création de la base de données
        public void CreatDatabase()
        {
            string requete = " CREATE TABLE IF NOT EXISTS `fichier` ( " +
                       "   `id_fichier` int(11) NOT NULL, " +
                       "   `src_fichier` varchar(1000) NOT NULL, " +
                       "   `num_cd_fichier` int(11) NOT NULL, " +
                       "   `id_film` int(11) NOT NULL, " +
                       "   PRIMARY KEY (`id_fichier`) " +
                       " ); " +

                       " CREATE TABLE IF NOT EXISTS `film` ( " +
                       "   `id_film` int(11) NOT NULL, " +
                       "   `titre_film` varchar(500) NOT NULL, " +
                       "   `synopsis_film` text NOT NULL, " +
                       "   `anne_film` varchar(4) NOT NULL, " +
                       "   `affiche_film` varchar(1000) NOT NULL, " +
                       "   `affiche_allocine` varchar(1000) NOT NULL, " +
                       "   `vue_film` tinyint(1) NOT NULL, " +
                       "   PRIMARY KEY (`id_film`) " +
                       " ); " +

                       " CREATE TABLE IF NOT EXISTS `genre` ( " +
                       "   `id_genre` int(11) NOT NULL, " +
                       "   `nom_genre` varchar(150) NOT NULL, " +
                       "   PRIMARY KEY (`id_genre`) " +
                       " ); " +

                       " CREATE TABLE IF NOT EXISTS `idgenre` ( " +
                       "   `id_genre` int(11) NOT NULL, " +
                       "   `id_film` int(11) NOT NULL " +
                       " ); ";


            //Création de la base de données
            this.Insert(requete);
        }
        #endregion

        #region Vide la base de données
        public void VideDataBase()
        {
            string requete = "DELETE FROM fichier; DELETE FROM film;"
                + "DELETE FROM genre; DELETE FROM idgenre;";

            this.Insert(requete);
        }
        #endregion

        #region Accesseur
        public string NameBdd
        {
            get { return this.nameBdd; }
        }
        #endregion
    }
}